package com.example.demo3;

import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.event.ActionEvent;
import javafx.fxml.FXML;
import javafx.fxml.Initializable;
import javafx.scene.control.*;
import javafx.scene.control.cell.PropertyValueFactory;
import javafx.scene.layout.VBox;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.net.URL;
import java.util.ResourceBundle;

import java.sql.*;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class HelloController  {

    @FXML
    private TableView<Driver> tableView;

    @FXML
    private TableColumn<Driver, Integer> driverId;
    @FXML
    private TableColumn<Driver,String> driverName;
    @FXML
    private TableColumn<Driver,String> driverMobile;
    @FXML
    private TableColumn<Driver,String> driverIdentifyNO;

    @FXML
    private TableColumn<Driver,String> driverAddress;
    @FXML
    private Button exportExcel;

    @FXML
    private Button zhenyiExportButton;



    @FXML
    void zhenyiExport(ActionEvent event) throws SQLException, IOException {
        Thread thread = new Thread(()->{
            try {
                Connection connection = dbConnector.connect();
                if(!connection.isClosed())
                    System.out.println("Succeeded connecting to the sqlserver database!");
                ResultSet resultSet = null;
                String sql = "\n" +
                        "select  tjrq 体检日期,XM 姓名,\n" +
                        "--case when jianchazhuangtai.ZT='0' then '未检' when jianchazhuangtai.ZT='1' then '已检' end 体检状态,\n" +
                        "case when XB='1' then '男' when XB='0' then '女' end 性别,NL 年龄,SFZH 身份证,shengao.JG 身高,tizhong.JG 体重,bmi.JG BMI指数,\n" +
                        "xueya.JG 血压,xinlv.JG 心率,xuehongdanbai.JG 血红蛋白,baixibao.JG 白细胞,xuexiaoban.JG 血小板,zhongxingli.JG 中性粒细胞绝对值,\n" +
                        "yinxue.JG 隐血,niaodanbai.JG 尿蛋白,ALT.JG ALT,AST.JG AST,jigan.JG 肌酐,niaosu.JG 尿素,niaosuan.JG 尿酸,xindiantu.JG 心电图,\n" +
                        "xiongpian.JG 胸片,xuetang.JG 血糖,zongdanguchun.JG 总胆固醇,dimidu.JG 低密度,ganyousanzhi.JG 甘油三酯,sejue.JG 色觉,shilizuo.JG 视力左,shiliyou.JG 视力右,\n" +
                        "jzshilizuo.JG 矫正视力左,jzshiliyou.JG 矫正视力右,tingli.JG 听阈值,chaosheng.JG 超声\n" +
                        "from futian_user.TJ_TJDJB tjdjb\n" +
                        "left join futian_user.VIEW_MXJLB shengao on tjdjb.TJBH = shengao.TJBH and shengao.TJXM = '010001'\n" +
                        "left join futian_user.VIEW_MXJLB tizhong on tjdjb.TJBH = tizhong.TJBH and tizhong.TJXM = '010002'\n" +
                        "left join futian_user.VIEW_MXJLB bmi on tjdjb.TJBH = bmi.TJBH and bmi.TJXM = '010007'\n" +
                        "left join futian_user.VIEW_MXJLB xueya on tjdjb.TJBH = xueya.TJBH and xueya.TJXM = '010003'\n" +
                        "left join futian_user.VIEW_MXJLB xinlv on tjdjb.TJBH = xinlv.TJBH and xinlv.TJXM = '010004'\n" +
                        "left join futian_user.VIEW_MXJLB xuehongdanbai on tjdjb.TJBH = xuehongdanbai.TJBH and xuehongdanbai.TJXM = '320003'\n" +
                        "left join futian_user.VIEW_MXJLB baixibao on tjdjb.TJBH = baixibao.TJBH and baixibao.TJXM = '320151'\n" +
                        "left join futian_user.VIEW_MXJLB xuexiaoban on tjdjb.TJBH = xuexiaoban.TJBH and xuexiaoban.TJXM = '320009'\n" +
                        "left join futian_user.VIEW_MXJLB zhongxingli on tjdjb.TJBH = zhongxingli.TJBH and zhongxingli.TJXM = '060267'\n" +
                        "left join futian_user.VIEW_MXJLB yinxue on tjdjb.TJBH = yinxue.TJBH and yinxue.TJXM = '060335'\n" +
                        "left join futian_user.VIEW_MXJLB niaodanbai on tjdjb.TJBH = niaodanbai.TJBH and niaodanbai.TJXM = '320030'\n" +
                        "left join futian_user.VIEW_MXJLB ALT on tjdjb.TJBH = ALT.TJBH and ALT.TJXM = '060316'\n" +
                        "left join futian_user.VIEW_MXJLB AST on tjdjb.TJBH = AST.TJBH and AST.TJXM = '060317'\n" +
                        "left join futian_user.VIEW_MXJLB jigan on tjdjb.TJBH = jigan.TJBH and jigan.TJXM = '060052'\n" +
                        "left join futian_user.VIEW_MXJLB niaosu on tjdjb.TJBH = niaosu.TJBH and niaosu.TJXM = '060318'\n" +
                        "left join futian_user.VIEW_MXJLB niaosuan on tjdjb.TJBH = niaosuan.TJBH and niaosuan.TJXM = '060053'\n" +
                        "left join futian_user.VIEW_MXJLB xindiantu on tjdjb.TJBH = xindiantu.TJBH and xindiantu.TJXM = '110020'\n" +
                        "left join futian_user.VIEW_MXJLB xiongpian on tjdjb.TJBH = xiongpian.TJBH and xiongpian.TJXM = '070007'\n" +
                        "left join futian_user.VIEW_MXJLB xuetang on tjdjb.TJBH = xuetang.TJBH and xuetang.TJXM = '060136'\n" +
                        "left join futian_user.VIEW_MXJLB zongdanguchun on tjdjb.TJBH = zongdanguchun.TJBH and zongdanguchun.TJXM = '060061'\n" +
                        "left join futian_user.VIEW_MXJLB dimidu on tjdjb.TJBH = dimidu.TJBH and dimidu.TJXM = '060063'\n" +
                        "left join futian_user.VIEW_MXJLB ganyousanzhi on tjdjb.TJBH = ganyousanzhi.TJBH and ganyousanzhi.TJXM = '060060'\n" +
                        "left join futian_user.VIEW_MXJLB sejue on tjdjb.TJBH = sejue.TJBH and sejue.TJXM = '040026'\n" +
                        "left join futian_user.VIEW_MXJLB shilizuo on tjdjb.TJBH = shilizuo.TJBH and shilizuo.TJXM = '040003'\n" +
                        "left join futian_user.VIEW_MXJLB shiliyou on tjdjb.TJBH = shiliyou.TJBH and shiliyou.TJXM = '040002'\n" +
                        "left join futian_user.VIEW_MXJLB jzshilizuo on tjdjb.TJBH = jzshilizuo.TJBH and jzshilizuo.TJXM = '040005'\n" +
                        "left join futian_user.VIEW_MXJLB jzshiliyou on tjdjb.TJBH = jzshiliyou.TJBH and jzshiliyou.TJXM = '040004'\n" +
                        "left join futian_user.VIEW_MXJLB tingli on tjdjb.TJBH = tingli.TJBH and tingli.TJXM = '050014'\n" +
                        "left join futian_user.VIEW_MXJLB chaosheng on tjdjb.TJBH = chaosheng.TJBH and chaosheng.TJXM = '230067'\n" +
                        "--left join futian_user.TJ_HISJK_RYXX jianchazhuangtai on tjdjb.TJBH = jianchazhuangtai.TJBH\n" +
                        "--where DWBH in (select BH from dbo.DWST where MC like '%振宜%') \n" +
                        "where DWBH = '6262'\n" +
                        "and tjdjb.TJBH > '2213562' \n" +
                        "and DateDiff(dd,TJRQ,getdate()) = 1   ";
                try {
                    Statement statement = connection.createStatement();
                    System.out.println("开始查询");
                    resultSet = statement.executeQuery(sql);
                    System.out.println("查询完毕");

                } catch (SQLException e) {
                    System.out.println("error msg:"+e.getMessage()+",errorCode:"+e.getErrorCode()+",sqlState:"+e.getSQLState());
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
                XSSFWorkbook workbook = new XSSFWorkbook();
                String userPath = System.getProperty("user.home");
                String desktopPath = userPath + "\\Desktop\\振宜当日.xlsx";
                System.out.println("desktop path:"+desktopPath);
                FileOutputStream fileOutputStream = new FileOutputStream(desktopPath);

                XSSFSheet sheet = workbook.createSheet("sheet1");

                Row headerRow = sheet.createRow(0);
                ResultSetMetaData metaData = resultSet.getMetaData();
                int columnCount = metaData.getColumnCount();

                for (int i = 1; i <= columnCount ; i++) {
                    Cell cell = headerRow.createCell(i-1);
                    cell.setCellValue(metaData.getColumnName(i));
                }

                int rowNum = 1;
                while (resultSet.next()){
                    Row dataRow = sheet.createRow(rowNum++);
                    for (int i = 1; i <= columnCount; i++) {
                        Cell cell = dataRow.createCell(i-1);
                        cell.setCellValue(resultSet.getString(i));
                    }
                }

                workbook.write(fileOutputStream);
                System.out.println("写入文件完毕完毕");

                workbook.close();

                fileOutputStream.close();
                resultSet.close();
                connection.close();
                System.out.println("关闭所有数据库连接");
            }catch (Exception e){
                System.out.println("error msg:"+e.getMessage());
            }

        });
        thread.start();

    }

    @FXML
    void exportExcel(ActionEvent event) throws IOException, SQLException {
        data = FXCollections.observableArrayList();
        // 先建立连接
        String URL="jdbc:mysql://rm-m5e36kg54gk44zn428o.mysql.rds.aliyuncs.com:3306/xm_delivery_pre?useUnicode=true&characterEncoding=UTF-8&userSSL=false&serverTimezone=GMT%2B8";//jdbc:mysql//服务器地址/数据库名
        String USER="jjj123";//用户名
        String PASSWORD="JjJGood963";//密码
        Connection con= null;
        System.out.println("连接中...");
        try {
            con = DriverManager.getConnection(URL, USER, PASSWORD);
            if(!con.isClosed())
                System.out.println("Succeeded connecting to the Database!");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
// 接着执行 sql语句
        ResultSet resultSet = null;
        String sql = "select * from driver limit 3,300";
        try {
            Statement statement = con.createStatement();
             resultSet = statement.executeQuery(sql);


        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        XSSFWorkbook workbook = new XSSFWorkbook();
        FileOutputStream fileOutputStream = new FileOutputStream("D:\\Users\\ysl\\Desktop\\司机信息.xlsx");

        XSSFSheet sheet = workbook.createSheet("sheet1");

        Row headerRow = sheet.createRow(0);
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();

        for (int i = 1; i <= columnCount ; i++) {
            Cell cell = headerRow.createCell(i-1);
            cell.setCellValue(metaData.getColumnName(i));
        }

        int rowNum = 1;
        while (resultSet.next()){
            Row dataRow = sheet.createRow(rowNum++);
            for (int i = 1; i <= columnCount; i++) {
                Cell cell = dataRow.createCell(i-1);
                cell.setCellValue(resultSet.getString(i));
            }
        }

        workbook.write(fileOutputStream);
        workbook.close();

        fileOutputStream.close();
        resultSet.close();



    }

    private ObservableList<Driver> data;

    @FXML
    public void initialize(){
        data = FXCollections.observableArrayList();
        // 先建立连接
        String URL="jdbc:mysql://rm-m5e36kg54gk44zn428o.mysql.rds.aliyuncs.com:3306/xm_delivery_pre?useUnicode=true&characterEncoding=UTF-8&userSSL=false&serverTimezone=GMT%2B8";//jdbc:mysql//服务器地址/数据库名
        String USER="jjj123";//用户名
        String PASSWORD="JjJGood963";//密码
        Connection con= null;
        System.out.println("连接中...");
        try {
            con = DriverManager.getConnection(URL, USER, PASSWORD);
            if(!con.isClosed())
                System.out.println("Succeeded connecting to the Database!");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
// 接着执行 sql语句
        String sql = "select * from driver limit 3,300";
        try {
            Statement statement = con.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()){
                //System.out.println(resultSet.getString("username"));
                data.add(new Driver(resultSet.getInt("user_id"),
                        resultSet.getString("username"),
                        resultSet.getString("driver_mobile"),
                        resultSet.getString("identify_number"),
                        resultSet.getString("address_detail")));
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        driverId.setCellValueFactory(new PropertyValueFactory<>("driverId"));
        driverName.setCellValueFactory(new PropertyValueFactory<>("driverName"));
        driverMobile.setCellValueFactory(new PropertyValueFactory<>("driverMobile"));
        driverIdentifyNO.setCellValueFactory(new PropertyValueFactory<>("driverIdentifyNO"));
        driverAddress.setCellValueFactory(new PropertyValueFactory<>("address"));

        tableView.setItems(data);


    }
}